package com.lcz.fruitweb.dao.impl;

import com.lcz.fruitweb.dao.FruitDao;
import com.lcz.fruitweb.util.JdbcUtil;
import model.Fruit;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 下级干活---实现接口
 *
 * @author 才智
 */
public class FruitDaoJdbcImpl implements FruitDao {

    @Override
    public boolean save(Fruit fruit) {
        Connection connection = null;
        PreparedStatement stmt = null;

        try {
            connection = JdbcUtil.getConnection();
            String sql = "insert into db_fruit values (default,?,?,?,?)";
            stmt = connection.prepareStatement(sql);
            stmt.setString(1, fruit.getName());
            stmt.setInt(2, fruit.getNumber());
            stmt.setDouble(3, fruit.getPrice());
            stmt.setString(4, fruit.getRemark());
            stmt.executeUpdate();
            return true;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtil.free(connection, stmt);
        }
        return false;
    }

    @Override
    public void del(int id) {
        Connection connection = null;
        Statement stmt = null;
        try {
            connection = JdbcUtil.getConnection();
            String sql = "delete from db_fruit where id=" + id;
            stmt = connection.createStatement();
            stmt.executeUpdate(sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtil.free(connection, stmt);
        }

    }

    @Override
    public Fruit findById(int id) {
        Connection connection = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            connection = JdbcUtil.getConnection();
            String sql = "select * from db_fruit where id=" + id;
            stmt = connection.createStatement();
            rs = stmt.executeQuery(sql);
            if (rs.next()) {
                Fruit fruit = new Fruit();
                fruit.setId(rs.getInt(1));
                fruit.setName(rs.getString("name"));
                fruit.setNumber(rs.getInt("number"));
                fruit.setPrice(rs.getDouble("price"));
                fruit.setRemark(rs.getString("remark"));
                return fruit;
            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtil.free(connection, stmt, rs);
        }
        return null;
    }

    @Override
    public boolean update(Fruit fruit) {
        Connection connection = null;
        Statement stmt = null;

        try {
            connection = JdbcUtil.getConnection();
            String sql = "update db_fruit set name='" + fruit.getName() + "',price=" + fruit.getPrice() + ",number=" + fruit.getNumber() + ",remark='" + fruit.getRemark() + "' where id=" + fruit.getId();

            stmt = connection.createStatement();
            stmt.executeUpdate(sql);

            //无错
            return true;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtil.free(connection, stmt);
        }
        return false;
    }

    @Override
    public List<Fruit> findAll() {
        Connection connection = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            connection = JdbcUtil.getConnection();
            String sql = "select * from db_fruit";
            stmt = connection.createStatement();
            rs = stmt.executeQuery(sql);
            List<Fruit> fruits = new ArrayList<>();
            while (rs.next()) {
                Fruit fruit = new Fruit();
                fruit.setId(rs.getInt(1));
                fruit.setName(rs.getString("name"));
                fruit.setNumber(rs.getInt("number"));
                fruit.setPrice(rs.getDouble("price"));
                fruit.setRemark(rs.getString("remark"));
                fruits.add(fruit);
            }
            return fruits;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtil.free(connection, stmt, rs);
        }
        return null;
    }
}
